package com.util;

import com.bean.SQLiteArgument;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Smile
 */
public class DBUtil implements AutoCloseable {
    final static Logger logger = LoggerFactory.getLogger(DBUtil.class);

    private Connection connection;
    private PreparedStatement statement;
    private ResultSet resultSet;
    private String dbFilePath;

    /**
     * 构造函数
     *
     * @param dbFilePath sqlite db 文件路径
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public DBUtil(String dbFilePath) throws Exception {
        this.dbFilePath = dbFilePath;
        connection = getConnection(dbFilePath);
    }

    /**
     * 获取数据库连接
     *
     * @param dbFilePath db文件路径
     * @return 数据库连接
     * @throws Exception
     */
    public Connection getConnection(String dbFilePath) throws Exception {
        File dbFile = new File(dbFilePath);
        if (!dbFile.exists()) {
            throw new Exception("案件文件不存在");
        }
        Class.forName("org.sqlite.JDBC");
        return DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
    }

    public Map<String, Object> executeQuery(String argument) throws Exception {
        List<Map<String, Object>> res = this.executeQuery(new SQLiteArgument(argument), true);
        return res.size() > 0 ? res.get(0) : null;
    }

    public Map<String, Object> executeQuery(SQLiteArgument argument) throws Exception {
        List<Map<String, Object>> res = this.executeQuery(argument, true);
        return res.size() > 0 ? res.get(0) : null;
    }

    public List<Map<String, Object>> executeQuerys(String sql) throws Exception {
        return this.executeQuery(new SQLiteArgument(sql), false);
    }

    public List<Map<String, Object>> executeQuerys(SQLiteArgument argument) throws Exception {
        return this.executeQuery(argument, false);
    }


    /**
     * 执行select查询，返回结果列表
     *
     * @param argument sql select 语句
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private List<Map<String, Object>> executeQuery(SQLiteArgument argument, boolean getFirst) throws Exception {
        List<Map<String, Object>> rsList = new ArrayList<>();
        try (PreparedStatement pst = this.getStatement(argument.getSQL())) {
            if (argument.getParameter() != null) {
                for (Map.Entry<Integer, Object> item : argument.getParameter().entrySet()) {
                    pst.setObject(item.getKey(), item.getValue());
                }
            }
            resultSet = pst.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String fieldName = metaData.getColumnName(i);

                    switch (metaData.getColumnTypeName(i)) {
                        case "NUMERIC":
                            rowData.put(fieldName, resultSet.getInt(fieldName));
                            break;
                        case "DECIMAL":
                            rowData.put(fieldName, resultSet.getBigDecimal(fieldName));
                            break;
                        case "BOOLEAN":
                            rowData.put(fieldName, resultSet.getBoolean(fieldName));
                            break;
                        default:
                            rowData.put(fieldName, resultSet.getString(fieldName));
                            break;
                    }
                }
                rsList.add(rowData);
                if (getFirst) {
                    break;
                }
            }
        } finally {
//            destroyed();
        }
        return rsList;
    }

    /**
     * 执行数据库更新sql语句
     *
     * @param argument
     * @return 更新行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public boolean executeUpdate(SQLiteArgument argument) throws Exception {
        try (PreparedStatement pst = getStatement(argument.getSQL())) {
            if (argument.getParameter() != null) {
                for (Map.Entry<Integer, Object> item : argument.getParameter().entrySet()) {
                    pst.setObject(item.getKey(), item.getValue());
                }
            }
            return pst.executeUpdate() > 0 ? true : false;
        } finally {
//            destroyed();
        }
    }

    /**
     * 执行多个sql更新语句
     *
     * @param sqls
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(String... sqls) throws Exception {
        try {
            for (String sql : sqls) {
                getStatement(sql).executeUpdate();
            }
        } finally {
//            destroyed();
        }
    }

    /**
     * 执行数据库更新 sql List
     *
     * @param sqls sql列表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdates(List<String> sqls) throws Exception {
        try {
            for (String sql : sqls) {
                this.getStatement(sql).executeUpdate();
            }
        } finally {
//            destroyed();
        }
    }

    public boolean executeUpdate(List<SQLiteArgument> sqls) throws Exception {
        try {
            for (SQLiteArgument item : sqls) {

                try (PreparedStatement pst = this.getStatement(item.getSQL())) {
                    if (item.getParameter() != null) {
                        for (Map.Entry<Integer, Object> par : item.getParameter().entrySet()) {
                            pst.setObject(par.getKey(), par.getValue());
                        }
                    }
                    pst.executeUpdate();
                }
            }
            return true;
        } finally {
//            destroyed();
        }
    }

    private Connection getConnection() throws Exception {
        if (null == connection) {
            connection = this.getConnection(dbFilePath);
        }
        return connection;
    }

    //    private Statement getStatement() throws Exception {
//        if (null == statement) {
//            statement = this.getConnection().createStatement();
//        }
//        return statement;
//    }
    private PreparedStatement getStatement(String sql) throws Exception {
        return this.getConnection().prepareStatement(sql);
//        if (null == statement) {
//            statement = this.getConnection().prepareStatement(sql);
//        }
//        return statement;
    }

    /**
     * 数据库资源关闭和释放
     */
    public void destroyed() {
        try {
            if (null != connection) {
                connection.close();
                connection = null;
            }

            if (null != statement) {
                statement.close();
                statement = null;
            }

            if (null != resultSet) {
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException e) {
            logger.error("Sqlite数据库关闭时异常", e);
        }
    }

    @Override
    public void close() {
        destroyed();
    }
}


